package backs.dataBase;

import backs.JSONChater.MyJSONSend;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class QAIDB
{
    static  Statement stmt = null;
    static Connection connection;
    public static void initConnect() throws SQLException {
        stmt = DataBaseController.getDBConnecterInstance().getConnection().createStatement();
        connection=stmt.getConnection();
    }


    public static boolean insertValue(String database,int id,String key,String value)
    {
        PreparedStatement pstmt=null;
        try
        {
            pstmt = connection.prepareStatement("INSERT INTO "+database+" (id,dataKey,value) VALUES (?,?,?)");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            pstmt.setString(3, value);
            if(pstmt.execute()) return false;
            return true;
        } catch (Exception e)
        {
            e.printStackTrace();
            MyJSONSend.sendToMe(e.getMessage());
            return false;
        }
        finally
        {
            try{pstmt.close();} catch (Exception e) {}
        }
    }

    public static boolean updateValue(String database,int id,String key,String value)
    {
        PreparedStatement pstmt=null;
        try
        {
            pstmt =connection.prepareStatement("update "+database+" set value= ? where id= ? and dataKey= ?");
            pstmt.setString(1, value);
            pstmt.setInt(2, id);
            pstmt.setString(3, key);
            if(pstmt.execute()) return false;
            return true;
        } catch (Exception e)
        {
            MyJSONSend.sendToMe(e.getMessage());
            return false;
        }
        finally
        {
            try{pstmt.close();} catch (Exception e) {}
        }
    }

    public static String getValue(String database,int id,String key)
    {
        ResultSet rs = null;
        PreparedStatement pstmt=null;
        try {
            pstmt = connection.prepareStatement("select * from "+database+" where id=? and dataKey=?");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            rs = pstmt.executeQuery();
            //ResultSetMetaData m=rs.getMetaData();
            rs.last();
            if(rs.getRow()<=0) return null;
            return rs.getString("value");
        } catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            try {rs.close();} catch (Exception e) {}
            try {pstmt.close();} catch (Exception e1) {}
        }
    }

    public static  List<String> getKeysLike(String database,int id,String key,String value)
    {
        ResultSet rs = null;
        PreparedStatement pstmt=null;
        try {
            pstmt = connection.prepareStatement("select * from "+database+" where id=? and dataKey like ? and value like ?");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            pstmt.setString(3, value);
            rs = pstmt.executeQuery();
            //ResultSetMetaData m=rs.getMetaData();
            rs.first();
            List<String> stringList=new ArrayList<>();
            if(rs.getRow()<=0) return stringList;
            while(true){
                stringList.add(rs.getString("dataKey"));
                if(!rs.isLast())rs.next();
                else break;
            }
            pstmt.close();
            return stringList;

        } catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            try {rs.close();} catch (Exception e) {e.printStackTrace();}
            try {pstmt.close();} catch (Exception e1) {e1.printStackTrace();}
        }
    }

    public static  List<String> getValuesLike(String database,int id,String key,String value)
    {
        ResultSet rs = null;
        PreparedStatement pstmt=null;
        try {
            pstmt = connection.prepareStatement("select * from "+database+" where id=? and dataKey like ? and value like ?");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            pstmt.setString(3, value);
            rs = pstmt.executeQuery();
            //ResultSetMetaData m=rs.getMetaData();
            rs.first();
            List<String> stringList=new ArrayList<>();
            if(rs.getRow()<=0) return stringList;
            while(true){
                stringList.add(rs.getString("value"));
                if(!rs.isLast())rs.next();
                else break;
            }
            pstmt.close();
            return stringList;

        } catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            try {rs.close();} catch (Exception e) {e.printStackTrace();}
            try {pstmt.close();} catch (Exception e1) {e1.printStackTrace();}
        }
    }

    public static  List<String> getValues(String database,int id,String key)
    {
        ResultSet rs = null;
        PreparedStatement pstmt=null;
        try {
            pstmt = connection.prepareStatement("select * from "+database+" where id=? and dataKey=?");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            rs = pstmt.executeQuery();
            //ResultSetMetaData m=rs.getMetaData();
            rs.first();
            List<String> stringList=new ArrayList<>();
            if(rs.getRow()<=0) return stringList;
            while(true){
                stringList.add(rs.getString("value"));
                if(!rs.isLast())rs.next();
                else break;
            }
            pstmt.close();
            return stringList;

        } catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            try {rs.close();} catch (Exception e) {e.printStackTrace();}
            try {pstmt.close();} catch (Exception e1) {e1.printStackTrace();}
        }
    }

    public static boolean delValue(String database,int id)
    {
        PreparedStatement pstmt=null;
        try
        {
            pstmt = connection.prepareStatement("delete from "+database+" where id= ?");
            pstmt.setInt(1, id);
            if(pstmt.execute()) return false;
            return true;
        } catch (Exception e)
        {
            MyJSONSend.sendToMe(e.getMessage());
            return false;
        }
        finally
        {
            try{pstmt.close();} catch (Exception e) {}
        }
    }

    public static boolean delValue(String database,int id,String key)
    {
        PreparedStatement pstmt=null;
        try
        {
            pstmt = stmt.getConnection().prepareStatement("delete from "+database+" where id= ? and dataKey= ?");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            if(pstmt.execute()) return false;
            return true;
        } catch (Exception e)
        {
            MyJSONSend.sendToMe(e.getMessage());
            return false;
        }
        finally
        {
            try{pstmt.close();} catch (Exception e) {}
        }
    }

    public static boolean delValue(String database,int id,String key,String value)
    {
        PreparedStatement pstmt=null;
        try
        {
            pstmt = connection.prepareStatement("delete from "+database+" where id= ? and dataKey= ? and value= ?");
            pstmt.setInt(1, id);
            pstmt.setString(2, key);
            pstmt.setString(3, value);
            if(pstmt.execute()) return false;
            return true;
        } catch (Exception e)
        {
            MyJSONSend.sendToMe(e.getMessage());
            return false;
        }
        finally
        {
            try{pstmt.close();} catch (Exception e) {}
        }
    }
}
